﻿using NPOI.SS.UserModel;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace com.nm.library.Excels
{
    /// <summary>
    /// Execl操作帮助类
    /// </summary>
    public static class ExcelUtil
    {
        //private static readonly CsharpHttpHelper.HttpHelper Http = new CsharpHttpHelper.HttpHelper();

        /// <summary>
        /// 拓展方法,生成EXECL
        /// </summary>
        /// <param name="info">EXECL相关信息</param>
        /// <returns>Execl路径</returns>
        public static string ExportExecl(this ExcelInfo info)
        {
            //1.获取列表对应数据

            //2.创建Execl文档

            //3.列映射

            //4.保存Execl
            return "/temp/" + info.FileName + ".xls";
        }

        /// <summary>
        /// 拓展方法,生成EXECL
        /// </summary>
        /// <param name="info"></param>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        /// <param name="dictFields">Excel字段映射及校验缓存</param>
        /// <returns></returns>
        public static MemoryStream ExportExeclStream(this ExcelInfo info, DataTable dt, string filePath, Dictionary<string, ImportVerify> dictFields, bool b)
        {
            //DictFields
            //1.获取列表对应数据
            //DataTable dt = GetDataTable(info);
            //1.获取Excel模板
            //var sheet= NPOIHelper.GetFirstSheet(filePath);
            var stream = new MemoryStream(File.ReadAllBytes(filePath));
            var workbook = NPOIHelper.InitWorkBook(stream, "");
            //2.创建Execl文档
            //return NPOIHelper.Export(dt, info.ColumnInfoList);
            var list = SetColumnEn(workbook, dictFields);
            if (b)
                return NPOIHelper.Export(dt, workbook, list);
            else
                return NPOIHelper.Export(dt, list);
        }

        /// <summary>
        /// 拓展方法,生成EXECL
        /// </summary>
        /// <param name="info"></param>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        /// <param name="dictFields">Excel字段映射及校验缓存</param>
        /// <returns></returns>
        public static string ExportExeclToDB(this ExcelInfo info, DataTable dt, string filePath, Dictionary<string, ImportVerify> dictFields, bool isNeedTemp, string fileName, int num, Dictionary<string, List<string>> dict)
        {
            //DictFields
            //1.获取列表对应数据
            //DataTable dt = GetDataTable(info);
            //1.获取Excel模板
            //var sheet= NPOIHelper.GetFirstSheet(filePath);
            var stream = new MemoryStream(File.ReadAllBytes(filePath));
            var workbook = NPOIHelper.InitWorkBook(stream, "");
            //2.创建Execl文档
            //return NPOIHelper.Export(dt, info.ColumnInfoList);
            var list = SetColumnEn(workbook, dictFields);
            if (isNeedTemp)
                return NPOIHelper.ExportToDB(dt, workbook, list, fileName, num, dict);
            else
                return NPOIHelper.ExportToDB(dt, list, fileName, num, dict);
        }

        /// <summary>
        /// 拓展方法,生成EXECL
        /// </summary>
        /// <param name="info"></param>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        /// <param name="dictFields">Excel字段映射及校验缓存</param>
        /// <returns></returns>
        public static string ExportExeclAll(this ExcelInfo info, IList<DataTable> dtList, IList<Dictionary<string, ImportVerify>> dictFieldsList, bool isNeedTemp, IList<string> fileNameList, int num, Dictionary<string, List<string>> dict)
        {
            //DictFields
            //1.获取列表对应数据
            //DataTable dt = GetDataTable(info);
            //1.获取Excel模板
            //var sheet= NPOIHelper.GetFirstSheet(filePath);
            //2.创建Execl文档
            //return NPOIHelper.Export(dt, info.ColumnInfoList);
            var list = SetColumnEn(dictFieldsList);
            return NPOIHelper.ExportAll(dtList, list, fileNameList, num, dict);
        }

        /// <summary>
        /// 拓展方法,生成错误信息EXECL
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        /// <param name="dictFields">Excel字段映射及校验缓存</param>
        /// <returns></returns>
        public static string ExportErrorExeclToDB(DataTable dt, string filePath, Dictionary<string, ImportVerify> dictFields, string fileName, int num)
        {
            //DictFields
            //1.获取列表对应数据
            //DataTable dt = GetDataTable(info);
            //1.获取Excel模板
            //var sheet= NPOIHelper.GetFirstSheet(filePath);
            var stream = new MemoryStream(File.ReadAllBytes(filePath));
            var workbook = NPOIHelper.InitWorkBook(stream, "");
            //2.创建Execl文档
            //return NPOIHelper.Export(dt, info.ColumnInfoList);
            var list = SetColumnEn(workbook, dictFields);
            return NPOIHelper.ExportErrorToDB(dt, workbook, list, fileName, num);
        }

        /// <summary>d
        /// dt与Excel模板列映射
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="dictFields"></param>
        /// <returns></returns>
        public static List<ColumnInfo> SetColumnEn(IWorkbook workbook, Dictionary<string, ImportVerify> dictFields)
        {
            if (dictFields == null)
            {
                return null;
            }
            //1.改变列名为英文字段名
            var columns = new List<string>();
            var list = new List<ColumnInfo>();
            var dt = new DataTable();
            //读取标题行
            var row = workbook.GetSheetAt(0).GetRow(5);
            for (int i = 0, length = row.LastCellNum; i < length; i++)
            {
                var cell = row.GetCell(i);
                if (cell == null)
                {
                    continue;
                }
                var objColumnName = GetCellVale(cell);
                dt.Columns.Add(objColumnName?.ToString() ?? "");
            }
            foreach (DataColumn dc in dt.Columns)
            {
                ImportVerify objVerify;
                if (!dictFields.TryGetValue(dc.ColumnName, out objVerify)) continue;
                if (objVerify == null) continue;
                columns.Add(objVerify.FieldName);
                list.Add(new ColumnInfo
                {
                    Field = objVerify.FieldName,
                    Header = objVerify.ColumnName,
                    Align = "left",
                    IsImage = objVerify.IsImage
                });
            }
            return list;
        }

        /// <summary>d
        /// dt与Excel模板列映射
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="dictFields"></param>
        /// <returns></returns>
        public static List<List<ColumnInfo>> SetColumnEn(IList<Dictionary<string, ImportVerify>> dictFieldsList)
        {
            if (dictFieldsList.Count <= 0)
            {
                return null;
            }
            var result = new List<List<ColumnInfo>>();
            foreach (var dictFields in dictFieldsList)
            {
                //1.改变列名为英文字段名
                var list = new List<ColumnInfo>();
                foreach (var dictField in dictFields)
                {
                    list.Add(new ColumnInfo
                    {
                        Field = dictField.Value.FieldName,
                        Header = dictField.Value.ColumnName,
                        Align = "left",
                        IsImage = dictField.Value.IsImage
                    });
                }
                result.Add(list);
            }
            return result;
        }

        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>单元格值</returns>
        private static object GetCellVale(ICell cell)
        {
            object obj = null;
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        obj = cell.DateCellValue;
                    }
                    else
                    {
                        obj = cell.NumericCellValue;
                    }
                    break;

                case CellType.String:
                    obj = cell.StringCellValue;
                    break;

                case CellType.Boolean:
                    obj = cell.BooleanCellValue;
                    break;

                case CellType.Formula:
                    obj = cell.CellFormula;
                    break;
            }
            return obj;
        }
    }
}